package com.tuanzi.loan.business.repository.statistics.impl;

import com.tuanzi.loan.business.repository.statistics.WechatAccountRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.vo.request.statistics.WechatAccountRequest;
import com.tuanzi.loan.vo.response.statistics.UnionLoginStatResponse;
import com.tuanzi.loan.vo.response.statistics.WechatAccountResponse;
import com.tuanzi.server.loan_base.util.DateUtils;
import org.apache.commons.lang.StringUtils;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.tuanzi.loan.generated.jooq.loan.Tables.LOAN_EXTRA_TEMPLATE_QRCODE_ATTENTION_RECORDS;

/**
 * @Auther: hequnayu
 * @Date: 2019/5/14 14:34
 * @Description:
 */
@Repository
public class WechatAccountRepositoryImpl extends JOOQBaseRepository implements WechatAccountRepository {

    @Override
    public Page<WechatAccountResponse> list(WechatAccountRequest searchRequest, Pageable pageable) {
        List<Object> paramList = new ArrayList<Object>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT \n" +
                "  a.CREATE_DATE,\n" +
                "  a.TEMPLATE_ID,\n" +
                "  c.name AS PEODUCE_ID,\n" +
                "  d.channel_name AS CHANNEL_ID,\n" +
                "  a.FUNCTION_ID,\n" +
                "  a.INFO_PAGE_UV,\n" +
                "  b.ATTENTION_NUM\n" +
                "FROM\n" +
                "  `stat`.`loan_wechat_extra_stat` a \n" +
                "  LEFT OUTER JOIN \n" +
                "    (SELECT \n" +
                "      DATE(r.CREATE_TIME) AS CREATE_DATE,\n" +
                "      r.TEMPLATE_ID,\n" +
                "      r.PEODUCE_ID,\n" +
                "      r.CHANNEL_ID,\n" +
                "      r.FUNCTION_ID,\n" +
                "      COUNT(r.ID) AS ATTENTION_NUM \n" +
                "    FROM\n" +
                "      (SELECT * FROM `loan`.`loan_extra_template_qrcode_attention_records` GROUP BY DATE(CREATE_TIME),to_user_name,from_user_name ) r \n" +
                "    GROUP BY DATE(r.CREATE_TIME),r.TEMPLATE_ID,r.PEODUCE_ID,r.CHANNEL_ID,r.FUNCTION_ID) b \n" +
                "    ON a.TEMPLATE_ID = b.TEMPLATE_ID \n" +
                "    AND a.PEODUCE_ID = b.PEODUCE_ID \n" +
                "    AND a.CHANNEL_ID = b.CHANNEL_ID \n" +
                "    AND a.FUNCTION_ID = b.FUNCTION_ID\n" +
                "    AND a.CREATE_DATE = b.CREATE_DATE" +
                "    LEFT OUTER JOIN `comm_product_prdid` c ON a.PEODUCE_ID  = c.prdid\n" +
                "    LEFT OUTER JOIN `comm_product_channel` d ON a.CHANNEL_ID  = d.channel_code where 1 = 1" );
        addCondition(sql, searchRequest, paramList);
        sql.append(" ORDER BY a.CREATE_DATE DESC ");

        //查询数量sql
        String countSql = "SELECT COUNT(*) AS num FROM (" + sql.toString() + ") AS d";
        Long count = dslContext.fetchOne(countSql, paramList.toArray()).into(Long.class);

        String getSql = sql + " LIMIT ?,?";
        paramList.add(pageable.getOffset());
        paramList.add(pageable.getPageSize());
        List<WechatAccountResponse> list = dslContext.fetch(getSql, paramList.toArray()).map(new WechatAccountRepositoryImpl.WechatStatRepositoryRecordMapper());
        return new PageImpl<WechatAccountResponse>(list, pageable,count);
    }

    private final class WechatStatRepositoryRecordMapper implements RecordMapper<Record, WechatAccountResponse> {

        @Override
        public WechatAccountResponse map(Record record) {
            WechatAccountResponse response=new WechatAccountResponse();
            response.setDateTime(record.get("CREATE_DATE",Date.class));
            response.setTemplateId(record.get("TEMPLATE_ID",Integer.class));
            response.setProductId(record.get("PEODUCE_ID",String.class));
            response.setChannelId(record.get("CHANNEL_ID",String.class));
            response.setActivityId(record.get("FUNCTION_ID",String.class));
            response.setPageUv(record.get("INFO_PAGE_UV",Integer.class));
//            response.setToScanUv(record.get("TO_SCAN_UV",Integer.class));
            response.setAttentionNum(record.get("ATTENTION_NUM",Integer.class));
            return response;
        }

    }

    @Override
    public void addCondition(StringBuilder sql, WechatAccountRequest searchRequest, List<Object> paramList) {
        if(null!=searchRequest.getStartDate()){
            sql.append(" and a.CREATE_DATE >= '"+DateUtils.formatDateToYyyyMMdd(searchRequest.getStartDate())+"'");
        }

        if(null!=searchRequest.getEndDate()){
            sql.append(" and a.CREATE_DATE <= '"+DateUtils.formatDateToYyyyMMdd(searchRequest.getEndDate())+"'");
        }

        if (!StringUtils.isEmpty(searchRequest.getProductId())){
            sql.append(" and a.PEODUCE_ID = '"+searchRequest.getProductId()+"'");
        }
        if (!StringUtils.isEmpty(searchRequest.getChannelId())){
            sql.append(" and a.CHANNEL_ID = '"+searchRequest.getChannelId()+"'");
        }
        if (!StringUtils.isEmpty(searchRequest.getActivityId())){
            sql.append(" and a.FUNCTION_ID = '"+searchRequest.getActivityId()+"'");
        }
        if (null != searchRequest.getTemplateId()){
            sql.append(" and a.TEMPLATE_ID = "+searchRequest.getTemplateId());
        }

    }

    @Override
    protected TableImpl getTable() {
        return LOAN_EXTRA_TEMPLATE_QRCODE_ATTENTION_RECORDS;
    }
}
